package com.gitee.melin.bee.core.jdbc.dialect.impl;

import com.gitee.melin.bee.core.jdbc.dialect.AbstractJdbcDialect;
import com.gitee.melin.bee.core.jdbc.enums.DataSourceType;
import com.gitee.melin.bee.core.jdbc.relational.BeeTableType;
import com.gitee.melin.bee.core.jdbc.relational.ConnectionInfo;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
import org.apache.commons.lang3.StringUtils;

public class OracleJdbcDialect extends AbstractJdbcDialect {
    protected static final List<String> EXCLUDED_SCHEMAS = Collections.unmodifiableList(Arrays.asList(
            "APPQOSSYS",
            "AUDSYS",
            "CTXSYS",
            "DVSYS",
            "DBSFWUSER",
            "DBSNMP",
            "GSMADMIN_INTERNAL",
            "LBACSYS",
            "MDSYS",
            "OJVMSYS",
            "OLAPSYS",
            "ORDDATA",
            "ORDSYS",
            "OUTLN",
            "SYS",
            "SYSTEM",
            "WMSYS",
            "XDB",
            "EXFSYS",
            "SYSMAN"));

    @Override
    public DataSourceType getDataSourceType() {
        return DataSourceType.ORACLE;
    }

    public OracleJdbcDialect(ConnectionInfo connectionInfo) {
        super(connectionInfo);
    }

    public OracleJdbcDialect(Connection connection) {
        super(connection);
    }

    @Override
    protected String testConnectionSql() {
        return "select 1 from dual";
    }

    @Override
    protected Driver getDriver() throws SQLException {
        return loadDriver(DataSourceType.ORACLE.getDriverClass());
    }

    @Override
    public List<String> getDatabases() {
        return this.getSchemas();
    }

    @Override
    protected boolean filterSchema(String schemaName) {
        return !EXCLUDED_SCHEMAS.contains(schemaName);
    }

    @Override
    protected String getSelectTablesSql(String tableName) {
        String sql = "SELECT A.TABLE_NAME,\n"
                + "       A.TABLE_TYPE,\n"
                + "       null      AS TABLE_ENGINE,\n"
                + "       A.COMMENTS TABLE_COMMENT,\n"
                + "       C.DATA_LENGTH AS DATA_SIZE,\n"
                + "       d.NUM_ROWS AS TABLE_ROWS,\n"
                + "       NULL AS TABLE_PARTITION,"
                + "       e.CREATED       CREATE_TIME,\n"
                + "       e.LAST_DDL_TIME UPDATE_TIME\n"
                + "FROM ALL_TAB_COMMENTS A\n"
                + "         LEFT JOIN (SELECT segment_name TABLE_NAME, sum(bytes) DATA_LENGTH FROM user_segments GROUP BY segment_name) c\n"
                + "                   ON C.TABLE_NAME = A.TABLE_NAME\n"
                + "         left join all_tables d ON d.OWNER = A.OWNER AND d.TABLE_NAME = A.TABLE_NAME\n"
                + "         left join ALL_OBJECTS e ON e.OBJECT_TYPE = 'TABLE' AND e.OWNER = A.OWNER AND e.OBJECT_NAME = A.TABLE_NAME\n"
                + "WHERE A.OWNER = :schemaName";

        if (StringUtils.isNotBlank(tableName)) {
            sql += " AND A.TABLE_NAME = :tableName";
        }
        return sql;
    }

    @Override
    protected String getSelectColumnsSql() {
        return "SELECT cols.COLUMN_NAME,\n"
                + "    CASE\n"
                + "        WHEN cols.data_type LIKE 'INTERVAL%%' THEN 'INTERVAL'\n"
                + "        ELSE REGEXP_SUBSTR(cols.data_type, '^[^(]+')\n"
                + "    END as DATA_TYPE,\n"
                + "    cols.data_type ||\n"
                + "        CASE\n"
                + "            WHEN cols.data_type IN ('VARCHAR', 'VARCHAR2', 'CHAR') THEN '(' || cols.data_length || ')'\n"
                + "            WHEN cols.data_type IN ('NVARCHAR2', 'NCHAR') THEN '(' || cols.char_length || ')'\n"
                + "            WHEN cols.data_type IN ('NUMBER') AND cols.data_precision IS NOT NULL AND cols.data_scale IS NOT NULL THEN '(' || cols.data_precision || ', ' || cols.data_scale || ')'\n"
                + "            WHEN cols.data_type IN ('NUMBER') AND cols.data_precision IS NOT NULL AND cols.data_scale IS NULL THEN '(' || cols.data_precision || ')'\n"
                + "            WHEN cols.data_type IN ('RAW') THEN '(' || cols.data_length || ')'\n"
                + "        END AS COLUMN_TYPE,\n"
                + "    cols.data_length AS DATA_LENGTH,\n"
                + "    cols.data_precision AS NUMERIC_PRECISION,\n"
                + "    cols.data_scale AS NUMERIC_SCALE,\n"
                + "    null AS DATETIME_PRECISION,\n"
                + "    com.comments AS COLUMN_COMMENT,\n"
                + "    cols.data_default AS DEFAULT_VALUE,\n"
                + "    CASE cols.nullable WHEN 'N' THEN 'NO' ELSE 'YES' END AS IS_NULLABLE\n"
                + "FROM\n"
                + "    all_tab_columns cols\n"
                + "LEFT JOIN\n"
                + "    all_col_comments com ON cols.table_name = com.table_name AND cols.column_name = com.column_name AND cols.owner = com.owner\n"
                + "WHERE\n"
                + "    cols.owner = :schemaName\n"
                + "    AND cols.table_name = :tableName\n"
                + "ORDER BY\n"
                + "    cols.column_id";
    }

    @Override
    protected String getDDLColumnName(BeeTableType type) {
        return "ct_script";
    }

    @Override
    protected String createTableSql(BeeTableType type, String schemaName, String tableName) {
        if (BeeTableType.TABLE == type) {
            return "SELECT DBMS_METADATA.GET_DDL('TABLE', '"
                    + tableName
                    + "', '"
                    + schemaName
                    + "') as ct_script FROM DUAL";
        } else if (BeeTableType.VIEW == type) {
            return "SELECT DBMS_METADATA.GET_DDL('VIEW', '"
                    + tableName
                    + "', '"
                    + schemaName
                    + "') as ct_script FROM DUAL";
        } else {
            return "SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW', '"
                    + tableName
                    + "', '"
                    + schemaName
                    + "') as ct_script FROM DUAL";
        }
    }
}
